//
//  SQLiteManager.swift
//  LunchDecider
//
//  Created by 王阳 on 2017/5/9.
//  Copyright © 2017年 WayneStudio. All rights reserved.
//  Documentation - https://github.com/stephencelis/SQLite.swift/blob/master/Documentation/Index.md#selecting-rows

import Foundation
import SQLite

class SQLiteManager {
    static let sharedInstance = SQLiteManager.init()    //创建单例
    var db: Connection!
    var table: Table!
    var id: Expression<Int64>!
    var name: Expression<String>!
    
    private init() {
        let dbPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first! + "/db.sqlite3"  //获取Document文件夹
        self.db = try? Connection(dbPath)    //设置数据库路径
        self.table = Table("restaurant")    //设置表名称
        self.id = Expression<Int64>("id")
        self.name = Expression<String>("name")
        
        do {
            try db.run(table.create(ifNotExists: true, block: { (table) in
                table.column(id, primaryKey: true)
                table.column(name)
            }))
            print("SQLite CREATE TABLE")
        } catch {
            print("SQLite CREATE TABLE ERROR:\(error)")
        }
    }
    
    //MARK: - 插入
    func insert(name: String = "New Name"){
        do {
            let row = try db.run(table.insert(self.name <- name))
            print("SQLite INSERT:\(name) at row:\(row)")
        }
        catch {
            print("SQLite INSERT ERROR:\(error)")
        }
    }
    
    //MARK: - 删除
    func delete(id: Int) {
        do {
            try db.run(table.filter(self.id == Int64(id)).delete())
            print("SQLite DELETE:\(id)")
        } catch {
            print("SQLite DELETE ERROR:\(error)")
        }
    }
    
    //MARK: - 更新
    func update(id: Int, name: String) {
        let filter = table.filter(self.id == Int64(id))
        do {
            try db.run(filter.update(self.name <- name))
            print("SQLite UPDATE:\(id) with name:\(name)")
        } catch {
            print("SQLite UPDATE ERROR:\(error)")
        }
    }
    
    //MARK: - 读取全部
    func selectAll() -> [(Int, String)]{
        var arr = [(Int, String)]()
        do {
            let rows = try db.prepare(table)
            for r in rows {
                arr.append((Int(r[self.id]), r[self.name]))
            }
            print("SQLite SELECT ALL")
        } catch {
            print("SQLite SELECT ERROR:\(error)")
        }
        return arr
    }
    

}
